from pylons import config
from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation, backref
from sqlalchemy.orm import scoped_session, sessionmaker
import datetime
from xadmin.model.objects import *

# Global session manager.  Session() returns the session object appropriate for the current web request.
Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config['pylons.g'].sa_engine))

#print Session

# Global metadata. If you have multiple databases with overlapping table names, you'll need a metadata for each
# database.
metadata = MetaData()

# Define a table.
tableprefix=config.get('table.prefix')

projects_table = Table(tableprefix+"projects", metadata,
    Column("id", types.Integer, primary_key=True, autoincrement=True),
    Column("name", types.String(255), nullable=False),
    Column("description", types.TEXT, default=''),
    Column("client", types.String(100), default=''),
    Column("start", types.Date),
    Column("end", types.Date),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
users_table = Table(tableprefix+"users", metadata,
    Column("id", types.Integer, primary_key=True, autoincrement=True),
    Column("username", types.String(100), nullable=False, unique=True),
    Column("password", types.String(100), nullable=False),
    Column("name", types.String(100), nullable=False),
    Column("last_name", types.String(100), nullable=False),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
	    )
client_reqs_table = Table(tableprefix + "client_requirements", metadata,
    Column("id", types.Integer, primary_key=True, autoincrement=True),
    Column("project_id", types.Integer, ForeignKey(tableprefix + 'projects.id')),
    Column("name", types.String(100), nullable=False),
    Column("description", types.TEXT, default=''),
    Column("estimation", types.Float),
    Column("release", types.String(100)),
    Column("iteration", types.String(100)),
    Column("requested_by", types.String(100), default=''),
    Column("assigned_to", types.String(100), default=''),
    Column("priority_id", types.Integer, ForeignKey(tableprefix + 'priorities.id')),
    Column("status_id", types.Integer, ForeignKey(tableprefix + 'statuses.id')),
    Column("impact_id", types.Integer, ForeignKey(tableprefix + 'impacts.id')),
    Column("type_id", types.Integer, ForeignKey(tableprefix + 'types.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
priorities_table = Table(tableprefix + "priorities", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False, unique=True),
    Column("description", types.TEXT)
    )
statuses_table = Table(tableprefix + "statuses", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False, unique=True),
    Column("description", types.TEXT)
    )
impacts_table = Table(tableprefix +"impacts", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False, unique=True),
    Column("description", types.TEXT)
    )
types_table = Table(tableprefix + "types", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False, unique=True),
    Column("description", types.TEXT)
    )
aceptance_tests_table = Table(tableprefix + "aceptance_tests", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False),
    Column("description", types.TEXT),
    Column("setup", types.TEXT),
    Column("input", types.TEXT),
    Column("output", types.TEXT),
    Column("project_id", types.Integer, ForeignKey(tableprefix + 'projects.id')),
    Column("requirement_id", types.Integer, ForeignKey(tableprefix + 'client_requirements.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
product_reqs_table = Table(tableprefix + "product_requirements", metadata,
    Column("id", types.Integer, primary_key=True, autoincrement=True),
    Column("project_id", types.Integer, ForeignKey(tableprefix + 'projects.id')),
    Column("name", types.String(100), nullable=False),
    Column("description", types.TEXT),
    Column("priority_id", types.Integer, ForeignKey(tableprefix + 'priorities.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
test_cases_table = Table(tableprefix + "test_cases", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("name", types.String(100), nullable=False),
    Column("description", types.TEXT),
    Column("setup", types.TEXT),
    Column("input", types.TEXT),
    Column("output", types.TEXT),
    Column("project_id", types.Integer, ForeignKey(tableprefix + 'projects.id')),
    Column("requirement_id", types.Integer, ForeignKey(tableprefix + 'product_requirements.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
cr_pr_table = Table(tableprefix + "cr_pr", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("cr_id", types.Integer, ForeignKey(tableprefix + 'client_requirements.id')),
    Column("pr_id", types.Integer, ForeignKey(tableprefix + 'product_requirements.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
product_table = Table(tableprefix + "products", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("project_id", types.Integer, ForeignKey(tableprefix + 'projects.id')),
    Column("name", types.String(100), nullable=False),
    Column("description", types.TEXT),
    Column("path", types.String(255)),
    Column("type", types.String(100)),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )
pr_prod_table = Table(tableprefix + "pr_prod", metadata,
    Column("id", types.Integer, primary_key = True, autoincrement=True),
    Column("pr_id", types.Integer, ForeignKey(tableprefix + 'product_requirements.id')),
    Column("prod_id", types.Integer, ForeignKey(tableprefix + 'products.id')),
    Column("created", types.DateTime, default=datetime.datetime.now()),
    Column("updated", types.DateTime, onupdate=datetime.datetime.now())
    )

# Map each class to its corresponding table.
mapper(Project, projects_table,
       properties={
                   'client_requirements':relation(ClientRequirement, backref=backref('project', uselist=False), cascade="all, delete, delete-orphan"),
                   'aceptance_tests':relation(AceptanceTest, backref=backref('project', uselist=False)),
                   'product_requirements':relation(ProductRequirement, backref=backref('project', uselist=False), cascade="all, delete, delete-orphan"),
                   'test_cases':relation(TestCase, backref=backref('project', uselist=False)),
                   'products':relation(Product, backref=backref('project', uselist=False), cascade="all, delete, delete-orphan")
                   }
       )
#mapper(User, users_table)
mapper(ClientRequirement, client_reqs_table,
       properties={
                   'priority':relation(Priority, uselist=False),
                   'status':relation(Status, uselist=False),
                   'impact':relation(Impact, uselist=False),
                   'type':relation(Type, uselist=False),
                   'aceptance_tests':relation(AceptanceTest, backref=backref('requirement', uselist=False), cascade="all, delete"),
                   'product_requirement_assoc':relation(CrPrAssociation, backref='client_requirements')
                   }
       )
mapper(Priority, priorities_table)
mapper(Status, statuses_table)
mapper(Type, types_table)
mapper(Impact, impacts_table)
mapper(AceptanceTest, aceptance_tests_table)
mapper(TestCase, test_cases_table)
mapper(Product, product_table)
mapper(CrPrAssociation, cr_pr_table,
       properties={
                   'product_requirements':relation(ProductRequirement, backref='client_requirement_assoc')
                   }
       )
mapper(ProductRequirement, product_reqs_table,
       properties={
                   'priority':relation(Priority, uselist=False),
                   'test_cases':relation(TestCase, backref='requirements', cascade="all, delete"),
                   'products_assoc':relation(PrProdAssociation, backref='product_requirements')
                   }
       )
mapper(PrProdAssociation, pr_prod_table,
       properties={
                   'products':relation(Product, backref='products_requirements_assoc')
                   }
       )
